import pymysql

class Mysql:
    def __init__(self,database,user,password,charset='utf8',port=3306,hostname='localhost'):
        self.host = hostname  #默认
        self.database = database
        self.user = user
        self.password = password
        self.charset = charset  #默认
        self.port = port  #默认

    """
    新增用户
    """
    def add(self,username,password,truename,phonenum,email):
        db = pymysql.connect(host=self.host,database=self.database,user=self.user,password=self.password,charset=self.charset,port=self.port)
        cur = db.cursor()
        sql = "insrt into user values(username=%s,realname=%s,password=%s,phone=%s,email=%s)"
        cur.execute(sql,(username,truename,password,phonenum,email))
        db.commit()
        cur.close()
        db.close()

    """
    删除用户
    """
    def delete(self,username):
        db = pymysql.connect(host=self.host, database=self.database, user=self.user, password=self.password,charset=self.charset, port=self.port)
        cur = db.cursor()
        sql = "delete from user where username = %s)"
        cur.execute(sql,(username))
        db.commit()
        cur.close()
        db.close()
    """
    更细用户
    """
    def update(self,username,truename,phonenum,email):
        db = pymysql.connect(host=self.host, database=self.database, user=self.user, password=self.password,charset=self.charset, port=self.port)
        cur = db.cursor()
        sql = "update user set realname = %s,phone = %s,email = %s where username = %s "
        cur.execute(sql,(truename,phonenum,email,username))
        db.commit()
        cur.close()
        db.close()

    """
    根据username查询  str类型
    """
    def select(self,username:str):
        db = pymysql.connect(host=self.host, database=self.database, user=self.user, password=self.password,
                             charset=self.charset, port=self.port)
        cur = db.cursor()
        sql = "select * from user where username = %s"
        cur.execute(sql, (username))
        data = cur.fetchmany()
        cur.close()
        db.close()
        return data
    """
    根据id查询  int类型
    """
    def select(self,id:int):
        db = pymysql.connect(host=self.host, database=self.database, user=self.user, password=self.password,
                             charset=self.charset, port=self.port)
        cur = db.cursor()
        sql = "select * from user where id = %s"
        cur.execute(sql, (id))
        data = cur.fetchmany()
        cur.close()
        db.close()
        return data
# print(Mysql('finance','root','root').select('lisi'))